/**
 * DaoUtil.java
 * xmlanalysis
 * 2011-8-30
 */
package com.lyjilu.dao.impl;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.dom4j.Document;

import com.lyjilu.constant.PopConstant;
import com.lyjilu.entity.ColumnMeta;
import com.lyjilu.util.CommonUtil;

/**
 * @author liuyongjie 2011-8-30 下午02:29:32
 */
public class DaoUtil {
	private static final String DRIVERCLASS = "oracle.jdbc.driver.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@192.168.1.110:1521:expbook";
	private static final String USER = "sec_race";
	private static final String PASSWORD = "race";

	private static final String DRIVERCLASSMYSQL = "com.mysql.jdbc.Driver";
	private static final String URLMYSQL = "jdbc:mysql://localhost:3306/lyjilu";
	private static final String USERMYSQL = "root";
	private static final String PASSWORDMYSQL = "root";

	public static void main(String[] args) throws Exception {
		Properties sysPro = System.getProperties();
		Object pathSeparator = sysPro.get(PopConstant.SYS_PATH_SEPARATOR);
		Object userDir = sysPro.get(PopConstant.SYS_USER_DIR);
		String cfgPath = userDir + "" + pathSeparator + PopConstant.CONFIG_PACKAGE
				+ pathSeparator + PopConstant.DBCONFIG;
		System.out.println(cfgPath);
		File file = new File(cfgPath);
		Document doc = CommonUtil.loadXMLFile(cfgPath);
		System.out.println(doc.getName());
		// List<Row> list = null;
		// list = DaoUtil.find("select * from pop_dep");
		// for (Iterator<Row> iterator = list.iterator(); iterator.hasNext();) {
		// Row row = (Row) iterator.next();
		// // System.out.println(row.get("id"));
		// Map<String, ColumnMeta> colMap = row.getColumnMetaMap();
		// Collection<ColumnMeta> col = colMap.values();
		// for (Iterator<ColumnMeta> iterator2 = col.iterator(); iterator2
		// .hasNext();) {
		// ColumnMeta columnMeta = (ColumnMeta) iterator2.next();
		// System.out.print(row.get(columnMeta.getName()) + "\t");
		// }
		// System.out.println();
		// }
	}

	public static List<Row> find(Connection conn, String sql) {
		long t1 = System.currentTimeMillis();
		ResultSet rs = null;
		PreparedStatement ps = null;
		List<Row> list = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int numberOfColumns = rsmd.getColumnCount();
			Map<String, ColumnMeta> map = new HashMap<String, ColumnMeta>();
			for (int i = 0; i < numberOfColumns; i++) {
				ColumnMeta columnMata = new ColumnMeta(
						rsmd.getColumnLabel(i + 1), 0,
						rsmd.getColumnType(i + 1),
						rsmd.getColumnTypeName(i + 1));
				map.put(rsmd.getColumnLabel(i + 1), columnMata);
			}
			String label = null;
			int type = 0;
			list = new ArrayList<Row>();
			while (rs.next()) {
				Row row = new Row();
				for (int i = 0; i < numberOfColumns; i++) {
					label = rsmd.getColumnLabel(i + 1);
					type = rsmd.getColumnType(i + 1);
					Object obj = DaoUtil.getObjectByName(rs, label, type);
					row.put(label, obj);
				}
				row.setColumnMetaMap(map);
				list.add(row);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DaoUtil.closeDao(rs, ps, conn);
		}
		long t2 = System.currentTimeMillis();
		System.out.println(t2 - t1);
		return list;

	}

	public static List<Row> find(String sql) throws Exception {
		return DaoUtil.find(DaoUtil.getConnection(), sql);
	}

	/**
	 * 关闭数据库资源
	 * 
	 * @param rs
	 *            ResultSet, 如果没有就传NULL
	 * @param ps
	 *            PreparedStatement, 如果没有就传NULL
	 * @param conn
	 *            Connection, 如果没有就传NULL
	 */
	public static void closeDao(ResultSet rs, PreparedStatement ps,
			Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				rs = null;
			}
		}
		if (ps != null)
			try {
				ps.close();
			} catch (SQLException e) {
				ps = null;
			}
		if (conn != null)
			try {
				conn.close();
			} catch (SQLException e) {
				conn = null;
				e.printStackTrace();
			}
	}

	public static Connection getConnection() throws Exception {
		Connection con = null;
		Class.forName(DRIVERCLASSMYSQL);
		con = DriverManager.getConnection(URLMYSQL, USERMYSQL, PASSWORDMYSQL);
		return con;
	}

	public static Object getObjectByName(ResultSet rs, String columnName,
			int type) throws SQLException {
		Object obj = null;
		switch (type) {
		case Types.VARCHAR:
			obj = rs.getString(columnName);
			break;
		case Types.NVARCHAR:
			obj = rs.getString(columnName);
			break;
		case Types.NUMERIC:
			obj = rs.getBigDecimal(columnName);
			break;
		case Types.BLOB:
			obj = rs.getByte(columnName);
			break;
		case Types.NCHAR:
			obj = rs.getString(columnName);
			break;
		case Types.TIME:
			obj = rs.getDate(columnName);
			break;
		case Types.TIMESTAMP:
			obj = rs.getTimestamp(columnName);
			break;
		case Types.FLOAT:
			obj = rs.getFloat(columnName);
			break;
		case Types.DOUBLE:
			obj = rs.getDouble(columnName);
			break;
		case Types.INTEGER:
			obj = rs.getInt(columnName);
			break;
		default:
			obj = rs.getString(columnName);
			break;
		}
		return obj;
	}
}
